/* *************************************************************************************************************/
/* ************************ Constructs a dataset for baseline proposal-level analysis  *************************/
/* *************************************************************************************************************/

data temp; set temp.iss_votes_ret;
  if 0 < abs(margin) <= .20 and issue ne '';

proc sort data = temp nodupkey out = proposals (keep=year mend exchcd shrcd rqdate wficn crsp_cl_grp winvote wt permno meetingdate mgmt_sponsored mgmt_win margin agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid fundfor fundagainst mgmtFor issForMgmt issue pwt voterequirement); 
  by wficn crsp_cl_grp mend permno meetingdate wt itemonagendaid; 

proc sort data = proposals out = unique_permno (keep=permno mend exchcd shrcd) nodupkey; by permno mend; 

proc sort data = proposals out = unique_proposals (keep=permno meetingdate mgmt_sponsored itemonagendaid issForMgmt) nodupkey; by permno meetingdate itemonagendaid; 

proc freq data = unique_proposals; tables mgmt_sponsored issForMgmt; run;

proc sort data = proposals out = unique (keep=wficn crsp_cl_grp mend exchcd shrcd) nodupkey; by wficn crsp_cl_grp mend; 

data unique; set unique;
  if month(mend) in (9,10,11,12) then mend2 = mdy(6, 30, year(mend));
  if 1 <= month(mend) <= 8 then mend2 = mdy(6, 30, year(mend)-1);
  format mend2 date9.;

proc sql;
  create table reg as
  select a.wficn, a.crsp_cl_grp, a.mend, b.permno, b.meetingdate, b.itemonagendaid, b.issForMgmt, b.iss_conform, 
    b.car12d_m_win, b.car12d_m, b.car7d_m, b.car7d_m_win, b.car5d_m, b.car5d_m_win, 
    b.WinVote, b.margin, b.mgmt_win, b.market_value_pr2, b.market_value_adj, b.pwt, b.pwt_adj
  from unique as a, test1 as b
  where a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and 1 <= intck('month', b.meetingdate, a.mend) <= 12; 
quit; * test1 is from 09Tests_fund_level.sas;

proc sort data = reg; by wficn crsp_cl_grp mend; where market_value_adj ne .;

proc means data = reg noprint; by wficn crsp_cl_grp mend;
  where car12d_m ne .; 
  var WinVote;
  output out = count2 (drop=_type_ _freq_) n = num_votes mean(iss_conform) = iss_conform;

proc means data = reg noprint; by wficn crsp_cl_grp mend;
  var car12d_m_win; weight market_value_adj; 
  output out = win_ret_1a (drop=_type_ _freq_) 
  mean = vote_alpha ;

proc means data = reg noprint; by wficn crsp_cl_grp mend; 
  var car12d_m_win; 
  output out = win_ret_1b (drop=_type_ _freq_) mean = vote_alpha_ew;

data measure; merge count2 win_ret_1a win_ret_1b; by wficn crsp_cl_grp mend;
run;

*** Vote with ISS in the past;
data all_proposals; set temp.iss_votes_ret;
  if issForMgmt = 1 then iss_win = mgmt_win;
  if issForMgmt = 0 then iss_win = -mgmt_win;
  month = month(meetingdate);
  yyyymm = year(meetingdate)*100+month;
  if issue ne '';
  keep wficn crsp_cl_grp year permno meetingdate issue issAgendaItemId itemonagendaid margin voteAgainstMgmt iss_conform issForMgmt mgmt_win nbr_shares cfacshr_rdate WinVote fundfor issfor;

proc sql;
  create table all_proposals as
  select a.*, b.*
  from all_proposals as a, temp.meetings_car_permno 
    (keep=permno meetingdate prc_pr2 cfacshr_pr2) as b
  where a.permno = b.permno and year(a.meetingdate) = year(b.meetingdate) and month(a.meetingdate) = month(b.meetingdate) and day(a.meetingdate) = day(b.meetingdate) and prc_pr2*nbr_shares*(cfacshr_rdate/cfacshr_pr2) > 0; 
quit;

data all_proposals; set all_proposals;
  if cfacshr_pr2 > 0 then market_value_pr2 = prc_pr2*nbr_shares*(cfacshr_rdate/cfacshr_pr2);
  if market_value_pr2 > 0;

proc sql;
  create table all_proposals as
  select a.wficn, a.crsp_cl_grp, a.mend, b.permno, b.issAgendaItemId, b.iss_conform, b.margin, b.issfor, b.fundfor
  from unique as a, all_proposals as b
  where a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and 1 <= intck('month', b.meetingdate, a.mend) <= 12; 
quit; 

proc sort data = all_proposals; by wficn crsp_cl_grp mend; 

proc means data = all_proposals noprint; by wficn crsp_cl_grp mend;
  var iss_conform;
  output out = temp.iss_conform (drop=_type_ _freq_) mean = iss_conform;
run;

%dummy(data=all_proposals, out=all_proposals, var=issAgendaItemId, prefix=p); 

proc reg data=all_proposals outest=outest noprint; by wficn crsp_cl_grp mend;
  model fundfor = issfor p1-p141 / rsquare press sse adjrsq;
run;
quit;

data temp.outest_r2; set outest; by wficn crsp_cl_grp mend; *coeff1;
 r2 = _rsq_;
 nobs = _P_ + _EDF_;
keep wficn crsp_cl_grp mend r2 nobs;
run;




data measure1; merge measure (in=a) temp.iss_conform temp.outest_r2; by wficn crsp_cl_grp mend; 
  if a;

proc sort data = temp.fundinfo nodupkey out = fundinfo; by wficn crsp_cl_grp caldt; where mtna ne .; 

proc sql;
  create table measure1 as
  select a.*, b.caldt, b.crsp_obj_cd2, b.mtna, b.mtna_adj, b.pct_common_eq, b.mgmt_no2
  from measure1 as a left join fundinfo as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and 1 <= intck('month', b.caldt, a.mend) <= 6;
quit;

proc sort; by wficn crsp_cl_grp mend caldt; 

data measure1; set measure1; by wficn crsp_cl_grp mend caldt; 
  if last.mend;
  if (index(crsp_obj_cd2, 'ED') = 1) and mtna >= 5 and num_votes > 10; 
  inverse_r2 = 1/r2;
  format mend date9.;

proc sql;
  create table measure1 as
  select a.*, b.num_ips_proxy_scaled, b.num_ips_proxy_ind
  from measure1 as a left join temp.mgmt_co_level as b
  on a.mgmt_no2 = b.mgmt_no2 and year(a.mend) = b.year+1;
quit;

proc means data = measure1 n mean median min max p1 p5 p10 p25 p75 p90 p95 p99 std; 
  var vote_alpha iss_conform nobs inverse_r2 r2 num_ips_proxy_scaled num_ips_proxy_ind;
run;

proc sort data = measure1; by mend;

proc rank data = measure1 out = measure1 groups = 5; by mend; 
  var vote_alpha vote_alpha_ew iss_conform inverse_r2 num_ips_proxy_scaled;
  ranks q_vote_alpha q_vote_alpha_ew q_iss_conform q_inverse_r2 q_num_ips; 
run;

proc sort data = measure1; by wficn crsp_cl_grp mend; 

data proposals2; merge proposals measure1; by wficn crsp_cl_grp mend;
  if wt ne .;

data temp.proposal_funds; set proposals2; 
run;


proc sort data = proposals2 nodupkey; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year wficn crsp_cl_grp; 

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; 
  output out = count3 (drop=_type_ _freq_) sum = mfo n = num_mf;

proc sort; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_vote_alpha in (4) and WinVote = 1; 
  output out = win_ret_2a (drop=_type_ _freq_) sum = wt_win_inf;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_vote_alpha in (4) and WinVote = 0; 
  output out = win_ret_2b (drop=_type_ _freq_) sum = wt_lose_inf;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where 0 <= q_vote_alpha <= 3 and WinVote = 1; 
  output out = win_ret_2c (drop=_type_ _freq_) sum = wt_win_other;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where 0 <= q_vote_alpha <= 3 and WinVote = 0; 
  output out = win_ret_2d (drop=_type_ _freq_) sum = wt_lose_other;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_vote_alpha in (0) and WinVote = 1; 
  output out = win_ret_2e (drop=_type_ _freq_) sum = wt_win_btmQ;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_vote_alpha in (0) and WinVote = 0; 
  output out = win_ret_2f (drop=_type_ _freq_) sum = wt_lose_btmQ;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_vote_alpha in (1,2,3) and WinVote = 1; 
  output out = win_ret_2g (drop=_type_ _freq_) sum = wt_win_mid;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_vote_alpha in (1,2,3) and WinVote = 0; 
  output out = win_ret_2h (drop=_type_ _freq_) sum = wt_lose_mid;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_vote_alpha_ew in (4) and WinVote = 1; 
  output out = win_ret_2i (drop=_type_ _freq_) sum = wt_win_inf_ew;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_vote_alpha_ew in (4) and WinVote = 0; 
  output out = win_ret_2j (drop=_type_ _freq_) sum = wt_lose_inf_ew;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where 0 <= q_vote_alpha_ew <= 3 and WinVote = 1; 
  output out = win_ret_2k (drop=_type_ _freq_) sum = wt_win_other_ew;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where 0 <= q_vote_alpha_ew <= 3 and WinVote = 0; 
  output out = win_ret_2l (drop=_type_ _freq_) sum = wt_lose_other_ew;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_iss_conform in (0) and WinVote = 1; 
  output out = win_ret_2m (drop=_type_ _freq_) sum = wt_win_active;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_iss_conform in (0) and WinVote = 0; 
  output out = win_ret_2n (drop=_type_ _freq_) sum = wt_lose_active;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_iss_conform not in (0) and WinVote = 1; 
  output out = win_ret_2o (drop=_type_ _freq_) sum = wt_win_passive;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where q_iss_conform not in (0) and WinVote = 0; 
  output out = win_ret_2p (drop=_type_ _freq_) sum = wt_lose_passive;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where num_ips_proxy_ind in (1) and WinVote = 1; 
  output out = win_ret_2q (drop=_type_ _freq_) sum = wt_win_high_num_ips;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where num_ips_proxy_ind in (1) and WinVote = 0; 
  output out = win_ret_2r (drop=_type_ _freq_) sum = wt_lose_high_num_ips;

proc means data = proposals2 noprint; by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  var wt; where num_ips_proxy_ind ne .; 
  output out = win_ret_2s (drop=_type_ _freq_) sum = num_ips_ind;

data proposals3; merge count3 win_ret_2a win_ret_2b win_ret_2c win_ret_2d win_ret_2e win_ret_2f win_ret_2g win_ret_2h  
  win_ret_2i win_ret_2j win_ret_2k win_ret_2l win_ret_2m win_ret_2n win_ret_2o win_ret_2p win_ret_2q win_ret_2r win_ret_2s; 
    by permno meetingdate mgmt_sponsored mgmt_win agendaGeneralDesc Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  if wt_win_inf = . then wt_win_inf = 0;
  if wt_lose_inf = . then wt_lose_inf = 0;
  net_win_informed = (wt_win_inf-wt_lose_inf);
  if wt_win_inf+wt_lose_inf > 0 then agree_informed = abs(wt_win_inf-wt_lose_inf)/(wt_win_inf+wt_lose_inf);

  if wt_win_other = . then wt_win_other = 0;
  if wt_lose_other = . then wt_lose_other = 0;
  net_win_uninformed = (wt_win_other-wt_lose_other);
  if wt_win_other+wt_lose_other > 0 then agree_uninformed = abs(wt_win_other-wt_lose_other)/(wt_win_other+wt_lose_other);

  if wt_win_btmQ = . then wt_win_btmQ = 0;
  if wt_lose_btmQ = . then wt_lose_btmQ = 0;
  net_win_btmQ = (wt_win_btmQ-wt_lose_btmQ);
  if wt_win_btmQ+wt_lose_btmQ > 0 then agree_btmQ = abs(wt_win_btmQ-wt_lose_btmQ)/(wt_win_btmQ+wt_lose_btmQ);

  if wt_win_mid = . then wt_win_mid = 0;
  if wt_lose_mid = . then wt_lose_mid = 0;
  net_win_mid = (wt_win_mid-wt_lose_mid);

  if wt_win_inf_ew = . then wt_win_inf_ew = 0;
  if wt_lose_inf_ew = . then wt_lose_inf_ew = 0;
  net_win_informed_ew = (wt_win_inf_ew-wt_lose_inf_ew);

  if wt_win_other_ew = . then wt_win_other_ew = 0;
  if wt_lose_other_ew = . then wt_lose_other_ew = 0;
  net_win_uninformed_ew = (wt_win_other_ew-wt_lose_other_ew);

  if wt_win_active = . then wt_win_active = 0;
  if wt_lose_active = . then wt_lose_active = 0;
  net_win_active = (wt_win_active-wt_lose_active);

  if wt_win_passive = . then wt_win_passive = 0;
  if wt_lose_passive = . then wt_lose_passive = 0;
  net_win_passive = (wt_win_passive-wt_lose_passive);

  if wt_win_high_num_ips = . and num_ips_ind > 0 then wt_win_high_num_ips = 0;
  if wt_lose_high_num_ips = . and num_ips_ind > 0 then wt_lose_high_num_ips = 0;
  net_win_high_num_ips = (wt_win_high_num_ips-wt_lose_high_num_ips);

  if issformgmt = 1 then iss_win = mgmt_win; else if issformgmt = 0 then iss_win = 1-mgmt_win;
  if num_mf >= 1; 
run;

proc sql;
  create table proposals4 as 
  select *
  from proposals3 as a left join temp.cst_matched as b
  on a.permno = b.permno and intck('day', a.meetingdate, b.meetingdate) = 0;
quit; run; * temp.cst_matched is from 06CST_CRSP_Meetings.sas;

proc sort data = proposals4 nodupkey; by permno meetingdate mgmt_sponsored mgmt_win Itemdesc issAgendaItemId itemonagendaid;
run;

proc sql;
  create table proposals4 as
  select a.*, b.*
  from proposals4 as a left join temp.meetings_car_permno as b
  on a.permno = b.permno and intck('day', a.meetingdate, b.meetingdate) = 0;
quit;

proc sort data = proposals4; by permno meetingdate mgmt_sponsored mgmt_win Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;

data proposals4; set proposals4; by permno meetingdate mgmt_sponsored mgmt_win Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  if last.year;
run;

*** Fraction of contentious votes in the past 12 months;
proc sql;
  create table proposal_count as
  select a.permno, a.meetingdate, b.num_votes, b.contested20, b.shrd_sponsored
  from proposals4 as a left join temp.stock_meetings as b
  on a.permno = b.permno and 1 <= intck('month', b.meetingdate, a.meetingdate) <= 12;
quit; * temp.stock_meetings is from 05Meetings_CAR.sas;

proc sort; by permno meetingdate ;

proc means noprint; by permno meetingdate;
  var num_votes contested20 shrd_sponsored;
  output out = proposal_count (drop=_type_ _freq_) sum = num_votes contested20 shrd_sponsored;
run;

proc sql;
  create table proposals4 as 
  select a.*, b.contested20 as n_contested, b.contested20/b.num_votes as frac_contested, b.shrd_sponsored as shrd_sponsored_pr1y
  from proposals4 as a left join proposal_count as b
  on a.permno = b.permno and a.meetingdate = b.meetingdate;
quit;

proc sql;
  create table proposals5 as 
  select a.*, b.issForMgmt as issForMgmt_meeting, b.shrd_sponsored as num_shrd_sponsored, b.mgmt_sponsored as num_mgmt_sponsored, b.iss_win_nc, b.mgmt_win_nc, b.num_votes_nc, b.mgmt_lose_nc_num, b.turnout_c
  from proposals4 as a left join temp.stock_meetings as b
  on a.permno = b.permno and year(a.meetingdate) = year(b.meetingdate) and month(a.meetingdate) = month(b.meetingdate) and day(a.meetingdate) = day(b.meetingdate);
quit;

proc sort data = proposals5; by permno meetingdate mgmt_sponsored mgmt_win Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;

data proposals5; set proposals5; by permno meetingdate mgmt_sponsored mgmt_win Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue margin year;
  if ind = . then ind = 48;
  if n_contested = . then n_contested = 0;
  ind_year = ind*10000+year;
  log_nanalyst = log(1+nanalyst);
  if 0 <= mgmt_win_nc < 1 then mgmt_lost_nc = 1; else mgmt_lost_nc = 0;

proc sort data = proposals5; by year;

proc rank data = proposals5 out = proposals5 groups = 5; by year;
  var io;
  ranks r_io; 

proc rank data = proposals5 out = proposals5 groups = 2; by year;
  var agree_informed;
  ranks high_agree; 

%winsor(dsetin=proposals5, dsetout=proposals5, byvar=none, vars=net_win_informed net_win_uninformed net_win_active net_win_passive net_win_high_num_ips 
  size mb leverage_b num_busseg capex sp500 roa mfo, type=winsor, pctl=1 99);

proc sort data=proposals5 out = temp.proposals nodupkey; by permno meetingdate itemonagendaid;
   where car63d_dgtw ~= . and iss_win ne . and mb ne . and leverage_b ne . and car63d_ff4 ne . and net_win_informed ne . and net_win_uninformed ne . and mgmt_win ne . and 
     iss_win ne . and size ne . and mb ne . and car_pr252d_m ne . and leverage_b ne . and capex ne . and sp500 ne . and mfo ne . and log_nanalyst ne .; 
run;

proc export data= temp.proposals 
            outfile= "D:\Dropbox\InformedVoting\proposals.dta" 
            dbms=stata replace;
run;
